#!/bin/bash
# File Name: count_col.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-04-09 10:52:46
# Function: 
#########################################################################
db_name="db_cmdb"
mysql_user="moshan"
mysql_passwd="xxxxxx"
ip_port_list="/home/moshan/cmdb/ip_port.list"
host_list="192.168.2.142"
mysql_port=3306
mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db_name}"
${mysql_comm} -NBe "select tsi.ip_app ,tdi.port from t_server_info tsi join t_db_instance tdi on tsi.server_id=tdi.server_id join t_db_ha_group_member tdhgm on tdhgm.member_id=tdi.instance_id join t_db_ha_group tdhg on tdhg.ha_group_id=tdhgm.ha_group_id;" 2>/dev/null|grep -v "^172"|awk '{print $1":"$2}' >${ip_port_list} 2>/dev/null
${mysql_comm} -NBe "select tsi.ip_app ,tdi.port from t_server_info tsi join t_db_instance tdi on tsi.server_id=tdi.server_id join t_db_ha_group_member tdhgm on tdhgm.member_id=tdi.instance_id join t_db_ha_group tdhg on tdhg.ha_group_id=tdhgm.ha_group_id where tdhgm.role_name='写节点'" 2>/dev/null|grep -v "^172"|awk '{print $1":"$2}' >${ip_port_list}
for ((i=1;i<=$(wc -l < ${ip_port_list});i++)) #i  in $(awk '{print }' <<< "${ip_port_list}")
do
    host_list="$(awk -F: '{print $1}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    mysql_port="$(awk -F: '{print $2}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    table_count="$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "select count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','sys','performance_schema');" 2>/dev/null)"
    col_count="$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "select count(*) from information_schema.COLUMNS where table_schema not in ('mysql','information_schema','sys','performance_schema');" 2>/dev/null)"
    distinct_col_count="$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "select count(distinct COLUMN_NAME) from information_schema.COLUMNS where table_schema not in ('mysql','information_schema','sys','performance_schema');" 2>/dev/null)"
    #mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "select sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024) 'data size(MB)' from information_schema.tables where DATA_LENGTH is not null and INDEX_LENGTH is not null;"
    echo "${table_count},${col_count},${distinct_col_count}"
done |awk -F, -v table=0 -v col=0 -v d_col=0 '{table += $1; col += $2; d_col += $3} END {print "table:"table",col:"col",d_col:"d_col}'
